package com.parleys.android.client.provider;

import android.content.Context;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.provider.BaseColumns;
import android.util.Log;

import com.parleys.android.client.provider.ParleysContract.Channels;
import com.parleys.android.client.provider.ParleysContract.ChannelsColumns;
import com.parleys.android.client.provider.ParleysContract.NewsColumns;
import com.parleys.android.client.provider.ParleysContract.ParleysBaseColumns;
import com.parleys.android.client.provider.ParleysContract.Presentations;
import com.parleys.android.client.provider.ParleysContract.PresentationsColumns;
import com.parleys.android.client.provider.ParleysContract.Spaces;
import com.parleys.android.client.provider.ParleysContract.SpacesColumns;

public class ParleysDatabase extends SQLiteOpenHelper {

	private static final String TAG = "ParleysDatabse";

    private static final String DATABASE_NAME = "parleys.db";

    private static final int VER_INITIAL_VERSION	= 1;

    private static final int DATABASE_VERSION = VER_INITIAL_VERSION;
    
    interface Tables {
        String NEWS 			= "news";
        String SPACES 			= "spaces";
        String CHANNELS			= "channels";
        String PRESENTATIONS	= "presentations";
    }

    private interface References {
        String SPACE_ID 		= "REFERENCES " + Tables.SPACES + "(" + Spaces.SPACE_ID + ")";
        String CHANNELD_ID 		= "REFERENCES " + Tables.CHANNELS + "(" + Channels.CHANNEL_ID + ")";
    }

	public ParleysDatabase(Context context) {
		super(context, DATABASE_NAME, null, DATABASE_VERSION);
	}

	@Override
	public void onCreate(SQLiteDatabase db) {
        Log.d(TAG, "onCreate()");
        
		db.execSQL("CREATE TABLE " + Tables.NEWS + " ("
				+ BaseColumns._ID + " INTEGER PRIMARY KEY AUTOINCREMENT,"
				+ NewsColumns.NEWS_ID + " INTEGER NOT NULL,"
				+ ParleysBaseColumns.CREATED_ON + " INTEGER NOT NULL,"
				+ ParleysBaseColumns.MODIFIED_ON + " INTEGER NOT NULL,"
				+ NewsColumns.TITLE + " TEXT NOT NULL,"
				+ NewsColumns.ITEM + " TEXT NOT NULL,"
				+ NewsColumns.AUTHOR + " TEXT NOT NULL,"
				+ NewsColumns.READ + " INTEGER NOT NULL DEFAULT 1,"
				+ "UNIQUE (" + NewsColumns.NEWS_ID + ") ON CONFLICT REPLACE)");

		db.execSQL("CREATE TABLE " + Tables.SPACES + " ("
				+ BaseColumns._ID + " INTEGER PRIMARY KEY AUTOINCREMENT,"
				+ SpacesColumns.SPACE_ID + " INTEGER NOT NULL,"
				+ ParleysBaseColumns.CREATED_ON + " INTEGER NOT NULL,"
				+ ParleysBaseColumns.MODIFIED_ON + " INTEGER NOT NULL,"
				+ SpacesColumns.NAME + " TEXT NOT NULL,"
				+ SpacesColumns.DESCRIPTION + " TEXT NOT NULL,"
				+ SpacesColumns.THUMBNAIL_URL + " TEXT,"
				+ SpacesColumns.PUBLIC + " INTEGER NOT NULL DEFAULT 1,"
				+ SpacesColumns.VISIBLE_IPAD + " INTEGER NOT NULL DEFAULT 0,"
				+ SpacesColumns.TOTAL_VIEWCOUNT + " INTEGER NOT NULL DEFAULT 0,"
				+ "UNIQUE (" + SpacesColumns.SPACE_ID + ") ON CONFLICT REPLACE)");

		db.execSQL("CREATE TABLE " + Tables.CHANNELS + " ("
				+ BaseColumns._ID + " INTEGER PRIMARY KEY AUTOINCREMENT,"
				+ ChannelsColumns.CHANNEL_ID + " INTEGER NOT NULL,"
				+ Channels.SPACE_ID + " INTEGER NOT NULL " + References.SPACE_ID + ","
				+ ParleysBaseColumns.CREATED_ON + " INTEGER NOT NULL,"
				+ ParleysBaseColumns.MODIFIED_ON + " INTEGER NOT NULL,"
				+ ChannelsColumns.NAME + " TEXT NOT NULL,"
				+ ChannelsColumns.DESCRIPTION + " TEXT NOT NULL,"
				+ ChannelsColumns.THUMBNAIL_URL + " TEXT,"
				+ ChannelsColumns.PUBLIC + " INTEGER NOT NULL DEFAULT 1,"
				+ ChannelsColumns.VISIBLE_IPAD + " INTEGER NOT NULL DEFAULT 0,"
				+ ChannelsColumns.IPHONE_ENABLED + " INTEGER NOT NULL DEFAULT 0,"
				+ ChannelsColumns.PODCAST_ENABLED + " INTEGER NOT NULL DEFAULT 0,"
				+ ChannelsColumns.TOTAL_VIEWCOUNT + " INTEGER NOT NULL DEFAULT 0,"
				+ ChannelsColumns.SUBSCRIBED + " INTEGER NOT NULL DEFAULT 0,"
				+ "UNIQUE (" + ChannelsColumns.CHANNEL_ID + ") ON CONFLICT REPLACE)");

		db.execSQL("CREATE TABLE " + Tables.PRESENTATIONS + " ("
				+ BaseColumns._ID + " INTEGER PRIMARY KEY AUTOINCREMENT,"
				+ PresentationsColumns.PRESENTATION_ID + " INTEGER NOT NULL,"
				+ Presentations.CHANNEL_ID + " INTEGER NOT NULL " + References.CHANNELD_ID + ","
				+ ParleysBaseColumns.CREATED_ON + " INTEGER NOT NULL,"
				+ ParleysBaseColumns.MODIFIED_ON + " INTEGER NOT NULL,"
				+ PresentationsColumns.TITLE + " TEXT NOT NULL,"
				+ PresentationsColumns.SUMMARY + " TEXT NOT NULL,"
				+ PresentationsColumns.THUMBNAIL_URL + " TEXT,"
				+ PresentationsColumns.MP3_URL + " TEXT,"
				+ PresentationsColumns.VISIBLE_IPAD + " INTEGER NOT NULL DEFAULT 0,"
				+ PresentationsColumns.IPHONE_ENABLED + " INTEGER NOT NULL DEFAULT 0,"
				+ PresentationsColumns.PODCAST_ENABLED + " INTEGER NOT NULL DEFAULT 0,"
				+ PresentationsColumns.TOTAL_VIEWS + " INTEGER NOT NULL DEFAULT 0,"
				+ PresentationsColumns.TOTAL_VOTES + " INTEGER NOT NULL DEFAULT 0,"
				+ PresentationsColumns.TOTAL_VOTEVALUE + " REAL NOT NULL DEFAULT 0,"
				+ PresentationsColumns.TOTAL_DOWNLOADS + " INTEGER NOT NULL DEFAULT 0,"
				+ PresentationsColumns.TOTAL_DURATION + " REAL NOT NULL DEFAULT 0,"
				+ "UNIQUE (" + PresentationsColumns.PRESENTATION_ID + ") ON CONFLICT REPLACE)");
	}

	@Override
	public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
        Log.d(TAG, "onUpgrade() from " + oldVersion + " to " + newVersion);
    }
	
}
